#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_dm.dm_signin_daycount
with t as (select class_id,dt,studying_student_count,
    sum(if(morning_signin = 0 and morning_signin = 1,1,0))as morning_signin_count,
                   sum(if(morning_signin = 1,1,0)) as morning_late_count,
                   sum(if(morning_leave = 1,1,0)) as morning_leave_count,
                   sum(if(afternoon_signin = 0 and afternoon_signin = 1,1,0))as afternoon_signin_count,
                   sum(if(afternoon_signin = 1,1,0)) as afternoon_late_count,
                   sum(if(afternoon_leave = 1,1,0)) as afternoon_leave_count,
                   sum(if(evening_signin = 0 and evening_signin = 1,1,0))as evening_signin_count,
                   sum(if(evening_signin = 1,1,0)) as evening_late_count,
                   sum(if(evening_leave = 1,1,0)) as evening_leave_count
from hive.edu_dws.dws_signin_daycount
    group by class_id,dt,studying_student_count
    )
select distinct  t.class_id,
                t.dt,
                t.studying_student_count,
                t.morning_signin_count,
                t.morning_late_count,
                t.morning_leave_count,
                t.afternoon_signin_count,
                t.afternoon_late_count,
                t.afternoon_leave_count,
                t.evening_signin_count,
                t.evening_late_count,
                t.evening_leave_count,
                (studying_student_count - t.morning_signin_count- t.morning_late_count - t.morning_leave_count)
           as morning_dis_count,
                (studying_student_count - t.afternoon_signin_count- t.afternoon_late_count - t.afternoon_leave_count)
       as afternoon_dis_count,
                (studying_student_count - t.evening_signin_count- t.evening_late_count - t.evening_leave_count)
       as evening_dis_count
from t;
"